package models

import (
	"bytes"
	"fmt"
	"strconv"
	"time"

	"github.com/beego/beego/v2/client/orm"
	"github.com/xuri/excelize/v2"
)

// 结算状态常量
const (
	SettlementStatusPending   = 0 // 待结算
	SettlementStatusCompleted = 1 // 已结算
	SettlementStatusCancelled = 2 // 已取消
)

// 财务交易扩展信息
type FinanceTransactionExt struct {
	StoreId         int64  `json:"store_id"`         // 门店ID
	StoreName       string `json:"store_name"`       // 门店名称
	MemberName      string `json:"member_name"`      // 会员姓名
	TransactionType string `json:"transaction_type"` // 交易类型：订单支付、充值、退款等
	RelatedId       int64  `json:"related_id"`       // 关联ID（订单ID、充值记录ID等）
	RelatedType     string `json:"related_type"`     // 关联类型：order、recharge、refund等
}

// Settlement 结算记录模型
type Settlement struct {
	BaseModel
	StoreId     int64  `orm:"index" json:"store_id"`        // 门店ID
	StartTime   int64  `json:"start_time"`                  // 结算开始时间
	EndTime     int64  `json:"end_time"`                    // 结算结束时间
	Amount      int64  `json:"amount"`                      // 结算金额（分）
	Status      int    `orm:"default(0)" json:"status"`     // 结算状态：0-待结算，1-已结算，2-已取消
	Description string `orm:"size(255)" json:"description"` // 结算说明

	// 非数据库字段
	StoreName string `orm:"-" json:"store_name"` // 门店名称
}

// TableName 设置表名
func (s *Settlement) TableName() string {
	return "ss_settlement"
}

// FinanceStatistics 财务统计数据
type FinanceStatistics struct {
	TotalRevenue     int64 `json:"total_revenue"`     // 总收入（分）
	TodayRevenue     int64 `json:"today_revenue"`     // 今日收入（分）
	YesterdayRevenue int64 `json:"yesterday_revenue"` // 昨日收入（分）
	WeekRevenue      int64 `json:"week_revenue"`      // 本周收入（分）
	MonthRevenue     int64 `json:"month_revenue"`     // 本月收入（分）
}

// GetFinanceStatistics 获取财务统计数据
func GetFinanceStatistics(filters map[string]interface{}) (*FinanceStatistics, error) {
	o := orm.NewOrm()
	stats := &FinanceStatistics{}

	// 构建基础查询条件
	whereClause := "is_deleted = 0"
	params := []interface{}{}

	// 应用过滤条件
	if storeId, ok := filters["store_id"].(int64); ok && storeId > 0 {
		whereClause += " AND store_id = ?"
		params = append(params, storeId)
	}
	if startTime, ok := filters["start_time"].(int64); ok && startTime > 0 {
		whereClause += " AND created_at >= ?"
		params = append(params, startTime)
	}
	if endTime, ok := filters["end_time"].(int64); ok && endTime > 0 {
		whereClause += " AND created_at <= ?"
		params = append(params, endTime)
	}

	// 获取总收入
	err := o.Raw("SELECT COALESCE(SUM(total_amount), 0) as total_revenue FROM ss_order WHERE "+whereClause+" AND status IN (2, 3)", params...).QueryRow(&stats.TotalRevenue)
	if err != nil {
		return nil, err
	}

	// 获取今日收入
	todayStart := time.Now().Format("2006-01-02") + " 00:00:00"
	todayEnd := time.Now().Format("2006-01-02") + " 23:59:59"
	todayStartUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", todayStart, time.Local)
	todayEndUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", todayEnd, time.Local)

	todayParams := append([]interface{}{}, params...)
	todayParams = append(todayParams, todayStartUnix.Unix(), todayEndUnix.Unix())
	err = o.Raw("SELECT COALESCE(SUM(total_amount), 0) as today_revenue FROM ss_order WHERE "+whereClause+" AND created_at >= ? AND created_at <= ? AND status IN (2, 3)", todayParams...).QueryRow(&stats.TodayRevenue)
	if err != nil {
		return nil, err
	}

	// 获取昨日收入
	yesterdayStart := time.Now().AddDate(0, 0, -1).Format("2006-01-02") + " 00:00:00"
	yesterdayEnd := time.Now().AddDate(0, 0, -1).Format("2006-01-02") + " 23:59:59"
	yesterdayStartUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", yesterdayStart, time.Local)
	yesterdayEndUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", yesterdayEnd, time.Local)

	yesterdayParams := append([]interface{}{}, params...)
	yesterdayParams = append(yesterdayParams, yesterdayStartUnix.Unix(), yesterdayEndUnix.Unix())
	err = o.Raw("SELECT COALESCE(SUM(total_amount), 0) as yesterday_revenue FROM ss_order WHERE "+whereClause+" AND created_at >= ? AND created_at <= ? AND status IN (2, 3)", yesterdayParams...).QueryRow(&stats.YesterdayRevenue)
	if err != nil {
		return nil, err
	}

	// 获取本周收入
	weekStart := time.Now().AddDate(0, 0, -int(time.Now().Weekday())).Format("2006-01-02") + " 00:00:00"
	weekEnd := time.Now().Format("2006-01-02") + " 23:59:59"
	weekStartUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", weekStart, time.Local)
	weekEndUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", weekEnd, time.Local)

	weekParams := append([]interface{}{}, params...)
	weekParams = append(weekParams, weekStartUnix.Unix(), weekEndUnix.Unix())
	err = o.Raw("SELECT COALESCE(SUM(total_amount), 0) as week_revenue FROM ss_order WHERE "+whereClause+" AND created_at >= ? AND created_at <= ? AND status IN (2, 3)", weekParams...).QueryRow(&stats.WeekRevenue)
	if err != nil {
		return nil, err
	}

	// 获取本月收入
	monthStart := time.Now().AddDate(0, 0, -int(time.Now().Day())+1).Format("2006-01-02") + " 00:00:00"
	monthEnd := time.Now().Format("2006-01-02") + " 23:59:59"
	monthStartUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", monthStart, time.Local)
	monthEndUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", monthEnd, time.Local)

	monthParams := append([]interface{}{}, params...)
	monthParams = append(monthParams, monthStartUnix.Unix(), monthEndUnix.Unix())
	err = o.Raw("SELECT COALESCE(SUM(total_amount), 0) as month_revenue FROM ss_order WHERE "+whereClause+" AND created_at >= ? AND created_at <= ? AND status IN (2, 3)", monthParams...).QueryRow(&stats.MonthRevenue)
	if err != nil {
		return nil, err
	}

	return stats, nil
}

// GetRevenueByDay 获取每日收入趋势
func GetRevenueByDay(filters map[string]interface{}) ([]map[string]interface{}, error) {
	o := orm.NewOrm()
	var result []map[string]interface{}

	// 构建基础查询条件
	whereClause := "is_deleted = 0"
	params := []interface{}{}

	// 应用过滤条件
	if storeId, ok := filters["store_id"].(int64); ok && storeId > 0 {
		whereClause += " AND store_id = ?"
		params = append(params, storeId)
	}
	if startTime, ok := filters["start_time"].(int64); ok && startTime > 0 {
		whereClause += " AND created_at >= ?"
		params = append(params, startTime)
	}
	if endTime, ok := filters["end_time"].(int64); ok && endTime > 0 {
		whereClause += " AND created_at <= ?"
		params = append(params, endTime)
	}

	// 查询每日收入
	sql := "SELECT FROM_UNIXTIME(created_at, '%Y-%m-%d') as day, COALESCE(SUM(total_amount), 0) as amount FROM ss_order WHERE " + whereClause + " AND status IN (2, 3) GROUP BY day ORDER BY day"
	var maps []orm.Params
	_, err := o.Raw(sql, params...).Values(&maps)
	if err != nil {
		return nil, err
	}

	// 构建结果
	for _, m := range maps {
		day := m["day"].(string)
		amount, _ := strconv.ParseInt(m["amount"].(string), 10, 64)
		result = append(result, map[string]interface{}{
			"day":    day,
			"amount": amount,
		})
	}

	return result, nil
}

// GetRevenueByStore 获取各门店收入分布
func GetRevenueByStore(filters map[string]interface{}) ([]map[string]interface{}, error) {
	o := orm.NewOrm()
	var result []map[string]interface{}

	// 构建基础查询条件
	whereClause := "o.is_deleted = 0"
	params := []interface{}{}

	// 应用过滤条件
	if startTime, ok := filters["start_time"].(int64); ok && startTime > 0 {
		whereClause += " AND o.created_at >= ?"
		params = append(params, startTime)
	}
	if endTime, ok := filters["end_time"].(int64); ok && endTime > 0 {
		whereClause += " AND o.created_at <= ?"
		params = append(params, endTime)
	}

	// 查询各门店收入
	sql := "SELECT o.store_id, s.name as store_name, COALESCE(SUM(o.total_amount), 0) as amount FROM ss_order o LEFT JOIN ss_store s ON o.store_id = s.id WHERE " + whereClause + " AND o.status IN (2, 3) GROUP BY o.store_id ORDER BY amount DESC"
	var maps []orm.Params
	_, err := o.Raw(sql, params...).Values(&maps)
	if err != nil {
		return nil, err
	}

	// 构建结果
	for _, m := range maps {
		storeId, _ := strconv.ParseInt(m["store_id"].(string), 10, 64)
		storeName := m["store_name"].(string)
		amount, _ := strconv.ParseInt(m["amount"].(string), 10, 64)
		result = append(result, map[string]interface{}{
			"store_id":   storeId,
			"store_name": storeName,
			"amount":     amount,
		})
	}

	return result, nil
}

// GetRevenueByVenue 获取各场地收入分布
func GetRevenueByVenue(filters map[string]interface{}) ([]map[string]interface{}, error) {
	o := orm.NewOrm()
	var result []map[string]interface{}

	// 构建基础查询条件
	whereClause := "o.is_deleted = 0"
	params := []interface{}{}

	// 应用过滤条件
	if storeId, ok := filters["store_id"].(int64); ok && storeId > 0 {
		whereClause += " AND o.store_id = ?"
		params = append(params, storeId)
	}
	if startTime, ok := filters["start_time"].(int64); ok && startTime > 0 {
		whereClause += " AND o.created_at >= ?"
		params = append(params, startTime)
	}
	if endTime, ok := filters["end_time"].(int64); ok && endTime > 0 {
		whereClause += " AND o.created_at <= ?"
		params = append(params, endTime)
	}

	// 查询各场地收入
	sql := "SELECT o.venue_id, v.name as venue_name, COALESCE(SUM(o.total_amount), 0) as amount FROM ss_order o LEFT JOIN ss_venue v ON o.venue_id = v.id WHERE " + whereClause + " AND o.status IN (2, 3) GROUP BY o.venue_id ORDER BY amount DESC"
	var maps []orm.Params
	_, err := o.Raw(sql, params...).Values(&maps)
	if err != nil {
		return nil, err
	}

	// 构建结果
	for _, m := range maps {
		venueId, _ := strconv.ParseInt(m["venue_id"].(string), 10, 64)
		venueName := m["venue_name"].(string)
		amount, _ := strconv.ParseInt(m["amount"].(string), 10, 64)
		result = append(result, map[string]interface{}{
			"venue_id":   venueId,
			"venue_name": venueName,
			"amount":     amount,
		})
	}

	return result, nil
}

// GetFinanceTransactionList 获取财务交易记录列表（扩展现有的Transaction模型）
func GetFinanceTransactionList(page, pageSize int, filters map[string]interface{}) ([]*Transaction, int64, error) {
	// 使用现有的GetTransactionList方法获取基础交易记录
	transactions, total, err := GetTransactionList(page, pageSize, filters)
	if err != nil {
		return nil, 0, err
	}

	// 为每个交易记录添加财务相关的扩展信息
	for _, t := range transactions {
		// 获取会员信息
		if t.MemberId > 0 {
			member, err := GetMemberById(t.MemberId, 0)
			if err == nil && member != nil {
				// 添加会员名称到交易记录
				t.RawData = fmt.Sprintf(`{"member_name":"%s"}`, member.Name)
			}
		}

		// 获取订单信息，添加门店ID和名称
		if t.OrderNo != "" {
			order, err := GetOrderByOrderNo(t.OrderNo)
			if err == nil && order != nil {
				// 获取门店信息
				store, err := GetStoreById(order.StoreId)
				if err == nil && store != nil {
					// 更新RawData字段，添加门店信息
					t.RawData = fmt.Sprintf(`{"member_name":"%s","store_id":%d,"store_name":"%s"}`,
						t.RawData, store.Id, store.Name)
				}
			}
		}
	}

	return transactions, total, nil
}

// GetTransactionDetail 获取交易记录详情
func GetTransactionDetail(id int64) (*Transaction, error) {
	// 使用现有的GetTransactionById方法获取基础交易记录
	transaction, err := GetTransactionById(id, 0)
	if err != nil {
		if err == orm.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	return transaction, nil
}

// GetFinanceTransactionDetail 获取财务交易记录详情（扩展现有的Transaction模型）
func GetFinanceTransactionDetail(id int64) (*Transaction, *FinanceTransactionExt, error) {
	// 使用现有的GetTransactionById方法获取基础交易记录
	transaction, err := GetTransactionById(id, 0)
	if err != nil {
		if err == orm.ErrNoRows {
			return nil, nil, nil
		}
		return nil, nil, err
	}

	// 创建财务交易扩展信息
	ext := &FinanceTransactionExt{}

	// 获取会员信息
	if transaction.MemberId > 0 {
		member, err := GetMemberById(transaction.MemberId, 0)
		if err == nil && member != nil {
			ext.MemberName = member.Name
		}
	}

	// 获取订单信息，添加门店ID和名称
	if transaction.OrderNo != "" {
		order, err := GetOrderByOrderNo(transaction.OrderNo)
		if err == nil && order != nil {
			ext.StoreId = order.StoreId

			// 获取门店信息
			store, err := GetStoreById(order.StoreId)
			if err == nil && store != nil {
				ext.StoreName = store.Name
			}

			// 设置交易类型和关联信息
			ext.TransactionType = "订单支付"
			ext.RelatedId = order.Id
			ext.RelatedType = "order"
		}
	}

	return transaction, ext, nil
}

 // ExportFinanceTransactions 导出财务交易记录
func ExportFinanceTransactions(filters map[string]interface{}) ([]byte, error) {
	// 配置
	const (
		maxRows  = 100000
		pageSize = 1000
	)
	// 构建 Excel
	f := excelize.NewFile()
	sheetName := "交易明细"
	f.SetSheetName("Sheet1", sheetName)

	sw, err := f.NewStreamWriter(sheetName)
	if err != nil {
		return nil, err
	}

	// 表头
	headers := []interface{}{
		"序号",
		"交易时间",
		"交易流水号",
		"订单号",
		"交易类型",
		"支付方式",
		"门店",
		"会员姓名",
		"金额(元)",
		"状态",
		"备注",
	}
	if err := sw.SetRow("A1", headers); err != nil {
		return nil, err
	}

	// 简单映射
	typeText := func(t int8) string {
		switch t {
		case 1:
			return "支付"
		case 2:
			return "退款"
		case 3:
			return "充值"
		case 4:
			return "提现"
		default:
			return fmt.Sprintf("%d", t)
		}
	}
	statusText := func(s int8) string {
		switch s {
		case 0:
			return "处理中"
		case 1:
			return "成功"
		case 2:
			return "失败"
		default:
			return fmt.Sprintf("%d", s)
		}
	}
	formatTime := func(ts int64) string {
		if ts <= 0 {
			return ""
		}
		return time.Unix(ts, 0).In(time.Local).Format("2006-01-02 15:04:05")
	}

	// 名称缓存，减少重复查询
	memberNameCache := map[int64]string{}
	storeNameCache := map[int64]string{}
	orderStoreCache := map[string]int64{} // orderNo -> storeId

	getMemberName := func(memberId int64) string {
		if memberId <= 0 {
			return ""
		}
		if name, ok := memberNameCache[memberId]; ok {
			return name
		}
		member, err := GetMemberById(memberId, 0)
		if err == nil && member != nil {
			memberNameCache[memberId] = member.Name
			return member.Name
		}
		memberNameCache[memberId] = ""
		return ""
	}
	getStoreNameByOrderNo := func(orderNo string) string {
		if orderNo == "" {
			return ""
		}
		// 命中缓存
		if storeId, ok := orderStoreCache[orderNo]; ok {
			if storeId <= 0 {
				return ""
			}
			if name, ok := storeNameCache[storeId]; ok {
				return name
			}
			store, err := GetStoreById(storeId)
			if err == nil && store != nil {
				storeNameCache[storeId] = store.Name
				return store.Name
			}
			storeNameCache[storeId] = ""
			return ""
		}
		// 查询订单
		order, err := GetOrderByOrderNo(orderNo)
		if err != nil || order == nil {
			orderStoreCache[orderNo] = 0
			return ""
		}
		orderStoreCache[orderNo] = order.StoreId
		if order.StoreId <= 0 {
			return ""
		}
		if name, ok := storeNameCache[order.StoreId]; ok {
			return name
		}
		store, err := GetStoreById(order.StoreId)
		if err == nil && store != nil {
			storeNameCache[order.StoreId] = store.Name
			return store.Name
		}
		storeNameCache[order.StoreId] = ""
		return ""
	}

	// 分页写入
	rowIdx := 2
	exported := 0
	page := 1
	for {
		if exported >= maxRows {
			break
		}
		transactions, _, err := GetTransactionList(page, pageSize, filters)
		if err != nil {
			_ = sw.Flush()
			return nil, err
		}
		if len(transactions) == 0 {
			break
		}

		for i, t := range transactions {
			if exported >= maxRows {
				break
			}
			// 数据映射
			idx := (page-1)*pageSize + i + 1
			created := formatTime(t.CreatedAt)
			txnNo := t.TransactionNo
			orderNo := t.OrderNo
			tType := typeText(t.Type)
			payMethod := t.PaymentChannel
			storeName := getStoreNameByOrderNo(orderNo)
			memberName := getMemberName(t.MemberId)
			amount := t.Amount // 已为元，保留两位小数
			stat := statusText(t.Status)
			remark := t.Remark

			row := []interface{}{
				idx,
				created,
				txnNo,
				orderNo,
				tType,
				payMethod,
				storeName,
				memberName,
				amount,
				stat,
				remark,
			}
			cell := fmt.Sprintf("A%d", rowIdx)
			if err := sw.SetRow(cell, row); err != nil {
				_ = sw.Flush()
				return nil, err
			}
			rowIdx++
			exported++
		}

		if len(transactions) < pageSize {
			break
		}
		page++
	}

	// 完成流式写入
	if err := sw.Flush(); err != nil {
		return nil, err
	}

	// 样式与布局
	if err := f.SetPanes(sheetName, `{"freeze":true,"x_split":0,"y_split":1,"top_left_cell":"A2","active_pane":"bottomLeft"}`); err != nil {
		return nil, err
	}
	// 列宽
	_ = f.SetColWidth(sheetName, "A", "A", 8)
	_ = f.SetColWidth(sheetName, "B", "B", 18)
	_ = f.SetColWidth(sheetName, "C", "D", 20)
	_ = f.SetColWidth(sheetName, "E", "H", 14)
	_ = f.SetColWidth(sheetName, "I", "I", 12)
	_ = f.SetColWidth(sheetName, "J", "K", 16)

	// 表头加粗
	if headerStyle, err := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{Bold: true},
	}); err == nil {
		_ = f.SetCellStyle(sheetName, "A1", "K1", headerStyle)
	}

	// 金额列数字格式
	lastDataRow := rowIdx - 1
	if lastDataRow >= 2 {
		if amtStyle, err := f.NewStyle(&excelize.Style{
			NumFmt: 4, // 内置 "#,##0.00"
		}); err == nil {
			_ = f.SetCellStyle(sheetName, "I2", fmt.Sprintf("I%d", lastDataRow), amtStyle)
		}
	}

	// 输出为字节流
	var buf bytes.Buffer
	if err := f.Write(&buf); err != nil {
		return nil, err
	}
	return buf.Bytes(), nil
}

// ExportTransactions 导出交易记录（作为ExportFinanceTransactions的别名）
func ExportTransactions(filters map[string]interface{}) ([]byte, error) {
	// 调用ExportFinanceTransactions函数
	return ExportFinanceTransactions(filters)
}

// GetSettlementList 获取结算记录列表
func GetSettlementList(page, pageSize int, filters map[string]interface{}) ([]*Settlement, int64, error) {
	o := orm.NewOrm()
	qs := o.QueryTable(new(Settlement)).Filter("is_deleted", 0)

	// 应用过滤条件
	if keyword, ok := filters["keyword"].(string); ok && keyword != "" {
		cond := orm.NewCondition()
		cond = cond.Or("description__contains", keyword)
		qs = qs.SetCond(cond)
	}
	if storeId, ok := filters["store_id"].(int64); ok && storeId > 0 {
		qs = qs.Filter("store_id", storeId)
	}
	if startTime, ok := filters["start_time"].(int64); ok && startTime > 0 {
		qs = qs.Filter("created_at__gte", startTime)
	}
	if endTime, ok := filters["end_time"].(int64); ok && endTime > 0 {
		qs = qs.Filter("created_at__lte", endTime)
	}
	if status, ok := filters["status"].(int); ok {
		qs = qs.Filter("status", status)
	}

	// 获取总数
	total, err := qs.Count()
	if err != nil {
		return nil, 0, err
	}

	// 分页查询
	var settlements []*Settlement
	_, err = qs.OrderBy("-created_at").Limit(pageSize).Offset((page - 1) * pageSize).All(&settlements)
	if err != nil {
		return nil, 0, err
	}

	// 填充门店信息
	for _, s := range settlements {
		if s.StoreId > 0 {
			store, err := GetStoreById(s.StoreId)
			if err == nil && store != nil {
				s.StoreName = store.Name
			}
		}
	}

	return settlements, total, nil
}

// GetSettlementDetail 获取结算记录详情
func GetSettlementDetail(id int64) (*Settlement, error) {
	o := orm.NewOrm()
	settlement := &Settlement{BaseModel: BaseModel{Id: id}}
	err := o.Read(settlement)
	if err != nil {
		if err == orm.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	// 填充门店信息
	if settlement.StoreId > 0 {
		store, err := GetStoreById(settlement.StoreId)
		if err == nil && store != nil {
			settlement.StoreName = store.Name
		}
	}

	return settlement, nil
}

// CreateSettlement 创建结算记录
func CreateSettlement(settlement *Settlement) (int64, error) {
	o := orm.NewOrm()
	settlement.CreatedAt = time.Now().Unix()
	settlement.UpdatedAt = settlement.CreatedAt
	return o.Insert(settlement)
}

// UpdateSettlementStatus 更新结算记录状态
func UpdateSettlementStatus(id int64, status int, description, operator string) error {
	o := orm.NewOrm()
	settlement := &Settlement{BaseModel: BaseModel{Id: id}}
	err := o.Read(settlement)
	if err != nil {
		return err
	}

	settlement.Status = status
	settlement.Description = description
	settlement.UpdatedBy = operator
	settlement.UpdatedAt = time.Now().Unix()

	_, err = o.Update(settlement, "Status", "Description", "UpdatedBy", "UpdatedAt")
	return err
}
